package com.lzj.demo.jdbc;


import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CrudByJdbc {
    @Test
    public void test()throws Exception {
        //新增
        doInsert();
        //查询
        doQuery();
        //修改
        doUpdate();
        //删除
        doDelete();
    }

    /***
     * 插入
     */
    public void doInsert()throws Exception{
        //模拟接收页面提交的参数
        int id = 1;
        String brandName = "王者荣耀";
        String companyName = "腾讯天美游戏工作室";
        String description = "Moba类推塔游戏";
        int ordered = 1;
        int status = 1;

        //定义insert sql语句
        String insertSql = "insert into tb_brand(id,brand_name,company_name,description,ordered,status)"
                +" values('"+ id + "','"
                + brandName + "','" +companyName + "','" + description + "','" + ordered + "','" + status +"')";
        System.out.println("insertSql: "+insertSql);
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        //执行SQL,并返回被影响的行数
        int count = statement.executeUpdate(insertSql);
        if(count > 0){
            System.out.println("操作结果:成功新增 "+count+" 行数据");
        }
        DBUtils.close(null,statement,conn);
    }

    /***
     * 查询
     */
    public void doQuery()throws Exception{
        //定义select sql语句
        String selectSql = "select * from tb_brand;";
        //获取Statement对象
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        //设置参数,执行SQL
        ResultSet rs = statement.executeQuery(selectSql);
        //装载brand对象
        List<Brand> list = new ArrayList<>();
        //遍历结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");

            //定义Brand对象接收查询结果
            Brand brand = Brand.builder()
                    .id(id)
                    .brandName(brandName)
                    .companyName(companyName)
                    .ordered(ordered)
                    .description(description)
                    .status(status)
                    .build();
            list.add(brand);
        }
        //在控制台打印结果
        System.out.println("查询结果集:"+list);
        DBUtils.close(rs,statement,conn);
    }

    /***
     * 修改
     */
    public void doUpdate()throws Exception{
        //定义update sql语句
        String updateSql = "update tb_brand set ordered = 2 where id = 1 ";
        //获取执行sql的对象Statement
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        //执行sql并返回受影响行数
        int count = statement.executeUpdate(updateSql);
        if(count > 0){
            System.out.println("操作结果:成功修改 "+count+" 行数据");
        }
        DBUtils.close(null,statement,conn);
    }

    /***
     * 删除
     */
    public void doDelete()throws Exception{
        //定义 delete sql语句
        String deleteSql = "delete from tb_brand where id = 1";
        Connection conn = DBUtils.getConnection();
        Statement statement = conn.createStatement();
        int count = statement.executeUpdate(deleteSql);
        if(count > 0){
            System.out.println("操作结果:成功删除 "+count+" 行数据");
        }
        DBUtils.close(null,statement,conn);
    }
}
